﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using FOModels.Loan;

namespace DataAccess.Loan
{
    public class LoanDAL
    {
        private SQLDataAccess db;
        public LoanDAL()
        {
            db = new SQLDataAccess();
        }
        public DataTable getloancatelist()
        {
            return db.exeSelect("Select * from LoanCategory");
        }
        public DataTable getloanplancatelist()
        {
            return db.exeSelect("Select * from LoanPlanCategory");
        }

        public DataTable getdefaultloan(int cateid, int planid)
        {
            return db.exeSelect("Select * from Loan sa join Company co on sa.CompanyID = co.CompanyID where sa.LoanCategoryID =" + cateid + " and sa.LoanPlanCategoryID = " + planid + "");
        }
        public DataTable getLoan(int cateid, int planid, string balance)
        {
            string ba = balance + "000000";
            double do_ba = double.Parse(ba);
            return db.exeSelect("Select * from Loan sa join Company co on sa.CompanyID = co.CompanyID where sa.LoanCategoryID =" + cateid + " and sa.LoanPlanCategoryID =" + planid + " and sa.MinimumBalance <= " + do_ba + " and sa.MaximumBalance >= " + do_ba + " ");
        }
        public DataTable getloanjson()
        {
            return db.exeSelect("Select * from Loan sa join Company co on sa.CompanyID = co.CompanyID join LoanCategory lo on sa.LoanCategoryID = lo.LoanCategoryID");
        }
        public DataTable getloanbyid(int loanid)
        {
            return db.exeSelect("Select * from Loan sa join Company co on sa.CompanyID = co.CompanyID where sa.LoanID =" + loanid + " ");
        }

        public int UpdateLoan(LoanModels models)
        {
            return db.exeUpdate("update Loan set LoanName = N'" + models.LoanName + "', MinimumBalance=" + double.Parse(models.MinimumBalance) + ",MaximumBalance=" + double.Parse(models.MaximumBalance) + ", Benefit = N'" + models.Benefit + "',InterestRate=" + models.InterestRate + ",CompanyID= " + models.CompanyID + ",LoanCategoryID=" + models.LoanCategoryID + ",LoanPlanCategoryID=" + models.LoanPlanCategoryID + ",Description=N'" + models.Description + "' where LoanID = " + models.LoanID + "");
        }

        public int InsertLoan(LoanModels models)
        {
            return db.exeUpdate("insert into Loan values (N'" + models.LoanName + "'," + double.Parse(models.MinimumBalance) + "," + double.Parse(models.MaximumBalance) + ",N'" + models.Benefit + "'," + models.InterestRate + "," + models.CompanyID + "," + models.LoanCategoryID + "," + models.LoanPlanCategoryID + ",N'" + models.Description + "')");
        }
        public int DeleteLoan(int LoanID)
        {
            return db.exeUpdate("Delete from Loan where LoanID = " + LoanID + "");
        }

        ///EN
        ///
        public DataTable getloancatelist_EN()
        {
            return db.exeSelect("Select * from LoanCategory_EN");
        }
        public DataTable getloanplancatelist_EN()
        {
            return db.exeSelect("Select * from LoanPlanCategory_EN");
        }

        public DataTable getdefaultloan_EN(int cateid, int planid)
        {
            return db.exeSelect("Select * from Loan_EN sa join Company_EN co on sa.CompanyID = co.CompanyID where sa.LoanCategoryID =" + cateid + " and sa.LoanPlanCategoryID = " + planid + "");
        }
        public DataTable getLoan_EN(int cateid, int planid, string balance)
        {
            string ba = balance + "000000";
            double do_ba = double.Parse(ba);
            return db.exeSelect("Select * from Loan_EN sa join Company_EN co on sa.CompanyID = co.CompanyID where sa.LoanCategoryID =" + cateid + " and sa.LoanPlanCategoryID =" + planid + " and sa.MinimumBalance <= " + do_ba + " and sa.MaximumBalance >= " + do_ba + " ");
        }
        public DataTable getloanjson_EN()
        {
            return db.exeSelect("Select * from Loan_EN sa join Company_EN co on sa.CompanyID = co.CompanyID join LoanCategory lo on sa.LoanCategoryID = lo.LoanCategoryID");
        }
        public DataTable getloanbyid_EN(int loanid)
        {
            return db.exeSelect("Select * from Loan_EN sa join Company_EN co on sa.CompanyID = co.CompanyID where sa.LoanID =" + loanid + " ");
        }

        public int UpdateLoan_EN(LoanModels models)
        {
            return db.exeUpdate("update Loan_EN set LoanName = N'" + models.LoanName + "', MinimumBalance=" + double.Parse(models.MinimumBalance) + ",MaximumBalance=" + double.Parse(models.MaximumBalance) + ", Benefit = N'" + models.Benefit + "',InterestRate=" + models.InterestRate + ",CompanyID= " + models.CompanyID + ",LoanCategoryID=" + models.LoanCategoryID + ",LoanPlanCategoryID=" + models.LoanPlanCategoryID + ",Description=N'" + models.Description + "' where LoanID = " + models.LoanID + "");
        }

        public int InsertLoan_EN(LoanModels models)
        {
            return db.exeUpdate("insert into Loan_EN values (N'" + models.LoanName + "'," + double.Parse(models.MinimumBalance) + "," + double.Parse(models.MaximumBalance) + ",N'" + models.Benefit + "'," + models.InterestRate + "," + models.CompanyID + "," + models.LoanCategoryID + "," + models.LoanPlanCategoryID + ",N'" + models.Description + "')");
        }
        public int DeleteLoan_EN(int LoanID)
        {
            return db.exeUpdate("Delete from Loan_EN where LoanID = " + LoanID + "");
        }
    }
}
